﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Web.Configuration;

namespace ForestryEnterprise
{
    public partial class AddTreeLocations : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                this.trees.Items.Add(new ListItem("--->"));
                this.locations.Items.Add(new ListItem("--->"));
                
                /*** Get trees ***/

                string statement = "select tree_id, tree_name from Tree";
                OleDbConnection connection = new OleDbConnection(WebConfigurationManager.ConnectionStrings["forestryDB"].ConnectionString);
                OleDbCommand command = new OleDbCommand(statement, connection);

                connection.Open();
                OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    ListItem newTree = new ListItem(reader["tree_name"].ToString(), reader["tree_id"].ToString());
                    this.trees.Items.Add(newTree);
                }
                reader.Close();
                connection.Close();

                /*** Get locations ***/

                statement = "select l_id, l_name from Location";
                command = new OleDbCommand(statement, connection);

                connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    ListItem newTree = new ListItem(reader["l_name"].ToString(), reader["l_id"].ToString());
                    this.locations.Items.Add(newTree);
                }
                reader.Close();
                connection.Close();
            }
        }

        protected void trees_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.trees.SelectedIndex > 0)
            {
                string statement = "select Location.* from Tree, Location, TreeHasLocation";
                statement += " where Tree.tree_id = TreeHasLocation.tree_id and Location.l_id = TreeHasLocation.l_id and Tree.tree_id = "+this.trees.SelectedValue;
                this.treeLocationsDB.SelectCommand = statement;
                this.treeLocations.DataBind();
            }
        }

        protected void addNewLoc_Click(object sender, EventArgs e)
        {
            if (this.trees.SelectedIndex > 0)
            {
                string statement = "select * from Location where l_country = @lCountry and l_area = @lArea and l_altitude = @lAlt and l_latitude = @lLat and l_longitude = @lLong";
                OleDbConnection connection = new OleDbConnection(WebConfigurationManager.ConnectionStrings["forestryDB"].ConnectionString);
                OleDbCommand command = new OleDbCommand(statement, connection);
                command.Parameters.AddWithValue("@lCountry", this.locCountry.Text);
                command.Parameters.AddWithValue("@lArea", this.locArea.Text);
                command.Parameters.AddWithValue("@lAlt", float.Parse(this.locAltitude.Text));
                command.Parameters.AddWithValue("@lLat", float.Parse(this.locLatitude.Text));
                command.Parameters.AddWithValue("@lLong", float.Parse(this.locLongitude.Text));
                connection.Open();
                OleDbDataReader reader = command.ExecuteReader();

                int locID = -1;

                if (reader.HasRows)
                {
                    reader.Read();
                    locID = int.Parse(reader["l_id"].ToString());
                }
                reader.Close();
                connection.Close();

                if (locID == -1)
                {
                    statement = "insert into Location(l_name, l_country, l_area, l_altitude, l_latitude, l_longitude)";
                    statement += " values(@lName, @lCountry, @lArea, @lAltitude, @lLatitude, @lLongitude)";
                    command = new OleDbCommand(statement, connection);
                    command.Parameters.AddWithValue("@lName", this.locName.Text);
                    command.Parameters.AddWithValue("@lCountry", this.locCountry.Text);
                    command.Parameters.AddWithValue("@lArea", this.locArea.Text);
                    command.Parameters.AddWithValue("@lAltitude", float.Parse(this.locAltitude.Text));
                    command.Parameters.AddWithValue("@lLatitude", float.Parse(this.locLatitude.Text));
                    command.Parameters.AddWithValue("@lLongitude", float.Parse(this.locLongitude.Text));
                    connection.Open();
                    command.ExecuteNonQuery();
                    connection.Close();

                    statement = "select top 1 l_id from Location order by l_id desc";
                    command = new OleDbCommand(statement, connection);
                    connection.Open();
                    reader = command.ExecuteReader();
                    reader.Read();
                    int latestLocID = int.Parse(reader["l_id"].ToString());
                    reader.Close();
                    connection.Close();

                    statement = "insert into TreeHasLocation values(@lID, @treeID)";
                    command = new OleDbCommand(statement, connection);
                    command.Parameters.AddWithValue("@lID", latestLocID);
                    command.Parameters.AddWithValue("@treeID", this.trees.SelectedValue);

                    connection.Open();
                    command.ExecuteNonQuery();
                    connection.Close();   
                }
                else
                {
                    statement = "insert into TreeHasLocation values(@lID, @treeID)";
                    command = new OleDbCommand(statement, connection);
                    command.Parameters.AddWithValue("@lID", locID);
                    command.Parameters.AddWithValue("@treeID", this.trees.SelectedValue);

                    connection.Open();
                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                    }
                    connection.Close();
                }

                statement = "select Location.* from Tree, Location, TreeHasLocation";
                statement += " where Tree.tree_id = TreeHasLocation.tree_id and Location.l_id = TreeHasLocation.l_id and Tree.tree_id = " + this.trees.SelectedValue;
                this.treeLocationsDB.SelectCommand = statement;
                this.treeLocations.DataBind();
            }
            else
            {
                err.Text = "Must select tree";
            }
        }

        protected void locations_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.locations.SelectedIndex > 0)
            {
                this.updateLoc.Enabled = true;

                string statement = "select * from Location where l_id = @lID";
                OleDbConnection connection = new OleDbConnection(WebConfigurationManager.ConnectionStrings["forestryDB"].ConnectionString);
                OleDbCommand command = new OleDbCommand(statement, connection);
                command.Parameters.AddWithValue("@lID", int.Parse(this.locations.SelectedValue));
                connection.Open();
                OleDbDataReader reader = command.ExecuteReader();
                reader.Read();
                this.locName.Text = reader["l_name"].ToString();
                this.locCountry.Text = reader["l_country"].ToString();
                this.locArea.Text = reader["l_area"].ToString();
                this.locAltitude.Text = reader["l_altitude"].ToString();
                this.locLatitude.Text = reader["l_latitude"].ToString();
                this.locLongitude.Text = reader["l_longitude"].ToString();
                reader.Close();
                connection.Close();
            }
            else
            {
                this.updateLoc.Enabled = false;
            }
        }

        protected void updateLoc_Click(object sender, EventArgs e)
        {
            string statement = "update Location set l_name = @lName, l_country = @lCountry, l_area = @lArea, l_altitude = @lAltitude, l_latitude = @lLatitude, l_longitude = @lLongitude";
            statement += " where l_id = @lID";
            OleDbConnection connection = new OleDbConnection(WebConfigurationManager.ConnectionStrings["forestryDB"].ConnectionString);
            OleDbCommand command = new OleDbCommand(statement, connection);
            command.Parameters.AddWithValue("@lName", this.locName.Text);
            command.Parameters.AddWithValue("@lCountry", this.locCountry.Text);
            command.Parameters.AddWithValue("@lArea", this.locArea.Text);
            command.Parameters.AddWithValue("@lAltitude", float.Parse(this.locAltitude.Text));
            command.Parameters.AddWithValue("@lLatitude", float.Parse(this.locLatitude.Text));
            command.Parameters.AddWithValue("@lLongitude", float.Parse(this.locLongitude.Text));
            command.Parameters.AddWithValue("@lID", this.locations.SelectedValue);
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();

            if (this.trees.SelectedIndex > 0)
            {
                statement = "select Location.* from Tree, Location, TreeHasLocation";
                statement += " where Tree.tree_id = TreeHasLocation.tree_id and Location.l_id = TreeHasLocation.l_id and Tree.tree_id = " + this.trees.SelectedValue;
                this.treeLocationsDB.SelectCommand = statement;
                this.treeLocations.DataBind();
            }
        }
    }
}